package com.micro.frog.fortune.util;

import cn.hutool.core.convert.Convert;
import cn.hutool.core.util.StrUtil;
import java.io.File;
import java.io.FileReader;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.TreeMap;
import java.util.concurrent.TimeUnit;
import javax.annotation.Nonnull;
import javax.sql.DataSource;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.jdbc.RuntimeSqlException;
import org.apache.ibatis.jdbc.ScriptRunner;
import org.springframework.beans.BeansException;
import org.springframework.context.ApplicationContext;
import org.springframework.context.ApplicationContextAware;
import org.springframework.stereotype.Component;

/**
 * @author jason.lv
 */
@Slf4j
@Component
public class DbUtil implements ApplicationContextAware {

    private static DataSource dataSource;

    @Override
    public void setApplicationContext(@Nonnull ApplicationContext context) throws BeansException {
        dataSource = context.getBean(DataSource.class);
    }

    private static final String TYPE_AUTO_SQL_RUN = "AUTO_SQL_RUN";
    private static final String TABLE_NAME_LOCK = "conf_lock";
    private static final String TABLE_VERSION = "conf_db_version";

    public static TreeMap<Integer, String> getSqlFile() {
        String sqlDir = File.separator + "sql" + File.separator;
        String path = System.getProperty("user.dir") + sqlDir;

        TreeMap<Integer, String> sqlFileMaps = new TreeMap<>();
        File file = new File(path);
        log.warn("开始从路径 {} 下找SQL脚本文件", path);
        if (file.isDirectory()) {
            File[] files = file.listFiles();
            if (files != null) {
                log.warn("发现SQL脚本文件数量 = {} ", files.length);
                for (File sqlFile : files) {
                    if (sqlFile.isFile() && StrUtil.endWith(sqlFile.getName(), ".sql")) {
                        String[] fileNameArray = sqlFile.getName().split("_");
                        if (fileNameArray.length < 1) {
                            throw new RuntimeSqlException("SQL文件名不符合规范::" + sqlFile.getName());
                        }
                        int index = Convert.toInt(fileNameArray[0]);
                        if (sqlFileMaps.get(index) != null) {
                            String[] names = sqlFileMaps.get(index).split("/");
                            String name = names[names.length - 1];
                            throw new RuntimeSqlException(String.format("发现【%s】与【%s】的序号相同，请检查！", sqlFile.getName(), name));
                        }
                        sqlFileMaps.put(index, sqlFile.getAbsolutePath());
                    }
                }
            }
        } else {
            log.warn(" {} 路径下没有脚本文件", path);
        }
        for (int key : sqlFileMaps.keySet()) {
            log.warn(key + " =======> " + sqlFileMaps.get(key));
        }
        return sqlFileMaps;
    }

    public static void execute() throws Exception {
        Connection connection = dataSource.getConnection();
        boolean beforeCheckResult = false;
        createTable(connection);
        int tryCount = 10;
        for (int i = 0; i < tryCount; i++) {
            if (!LockUtil.addLock(TYPE_AUTO_SQL_RUN, 1800)) {
                log.warn("有其他服务在执行sql脚本。。。。。。。。。 {}/{}", (i + 1), tryCount);
                log.warn("等待3s后再次执行");
                TimeUnit.SECONDS.sleep(3);
            } else {
                beforeCheckResult = true;
                break;
            }
        }
        if (!beforeCheckResult) {
            throw new Exception(String.format("请检查表:%s 数据是否有 type= %s 存在！BYE！", TABLE_NAME_LOCK, TYPE_AUTO_SQL_RUN));
        }

        Statement statement = connection.createStatement();
        ResultSet resultSet = null;
        try {
            ScriptRunner runner = new ScriptRunner(connection);
            runner.setStopOnError(true);

            int version = -1;

            if (tableIsCreated(connection, TABLE_VERSION)) {
                resultSet = statement.executeQuery("select ifnull(max(version),-1) version from conf_db_version");
                if (resultSet.next()) {
                    version = resultSet.getInt("version");
                }
            }

            TreeMap<Integer, String> sqlFileMaps = DbUtil.getSqlFile();

            log.warn("当前数据库库版本号 = {}", version);

            List<String> updatedFileList = new ArrayList<>();
            for (int v : sqlFileMaps.keySet()) {
                if (v > version) {
                    String sqlFileName = sqlFileMaps.get(v);
                    String[] names = sqlFileName.split("/");
                    String name = names[names.length - 1];
                    updatedFileList.add(name);

                    log.warn("==========开始执行SQL脚本:{}===========================================", name);
                    runner.runScript(new FileReader(sqlFileName));
                    log.warn("==========完成执行SQL脚本:{}===========================================", name);

                    String sql = String.format(
                        "insert into conf_db_version (version,`name`,`status`,create_time,update_time) values (%d,'%s',1,%d,%d)",
                        v, name, System.currentTimeMillis(), System.currentTimeMillis());
                    statement.executeUpdate(sql);
                }
            }

            log.warn("本次成功更新的SQL脚本文件有 {} 个：", updatedFileList.size());
            for (String fileName : updatedFileList) {
                log.warn(fileName);
            }
        } finally {
            close(connection, statement, resultSet);
            log.warn("执行完毕，开始释放 {}", TYPE_AUTO_SQL_RUN);
            LockUtil.removeLock(TYPE_AUTO_SQL_RUN);
        }
    }

    private static void createTable(Connection connection) throws Exception {
        Statement statement = connection.createStatement();
        try {
            if (!tableIsCreated(connection, TABLE_NAME_LOCK)) {
                // 创建表
                String sql = "create table `conf_lock` (\n"
                    + "  `id` bigint(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键，自增',\n"
                    + "  `host_name` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '名称',\n"
                    + "  `type` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '类型',\n"
                    + "  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',\n"
                    + "  PRIMARY KEY (`id`),\n" + "  UNIQUE KEY `unique_form_code` (`type`)\n"
                    + ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='同步锁表';";
                statement.executeUpdate(sql);
            }
        } catch (SQLException e) {
            log.error(e.getMessage(), e);
        } finally {
            close(null, statement, null);
        }
    }

    private static boolean tableIsCreated(Connection connection, String tableName) throws Exception {
        Statement statement = connection.createStatement();
        ResultSet resultSet = null;
        try {
            String hasTableSql = "select count(*) _count from information_schema.TABLES where TABLE_SCHEMA=(select database()) and table_name='%s'";
            resultSet = statement.executeQuery(String.format(hasTableSql, tableName));
            if (resultSet.next()) {
                return resultSet.getInt("_count") > 0;
            }
            return false;
        } finally {
            close(null, statement, resultSet);
        }
    }

    private static void close(Connection connection, Statement statement, ResultSet resultSet) {
        try {
            if (resultSet != null) {
                resultSet.close();
            }
        } catch (Exception ignored) {
        }
        try {
            if (statement != null) {
                statement.close();
            }
        } catch (Exception ignored) {
        }
        try {
            if (connection != null) {
                connection.commit();
                connection.close();
            }
        } catch (Exception ignored) {
        }
    }

}
